#!/usr/bin/env python
# -*- encoding: utf-8 -*-
# Project: spd-sxmcc
"""
@author: lyndon
@time Created on 2018/11/7 11:39
@desc
"""

import psycopg2
import time

class PostgreOpr():
    def __init__(self, db_infos):
        self.pgconn = psycopg2.connect(dbname=db_infos.get('database'), user=db_infos.get('username'),
                                       password=db_infos.get('password'), host=db_infos.get('host'),
                                       port=db_infos.get('port'))
        self.pgcursor = self.pgconn.cursor()

    def __del__(self):
        self.pgcursor.close()
        self.pgconn.close()

    def commit(self):
        self.pgconn.commit()

    def close(self):
        self.pgconn.close()

    def rollback(self):
        self.pgconn.rollback()

    def getddl(self, schema_name, table_name):
        sql = ("select column_name, data_type, character_maximum_length, numeric_precision, numeric_scale" \
                " from information_schema.columns t1" \
                " where table_schema='%s'" \
                " and table_name='%s'" \
                " order by ordinal_position" % (schema_name, table_name))
        # pgcursor = self.pgconn.cursor()
        self.pgcursor.execute(sql)
        result = self.pgcursor.fetchall()
        # pgcursor.close()
        return result

    def getSynTask(self):
        sql = ("select t1.job_id,t1.job_type,t1.job_detail,t1.user_name,t1.table_name,t1.status,t1.rate,t2.resultdb" \
             " from hive_job t1" \
             " inner join spd_user t2" \
             " on t1.user_name=t2.username")
        # pgcursor = self.pgconn.cursor()
        self.pgcursor.execute(sql)
        result = self.pgcursor.fetchall()
        # pgcursor.close()
        return result

    def execsql(self, sql):
        self.pgcursor.execute(sql)
        return 0

def getconninfo(login_str):
    db_infos = {}
    dbtype, dbinfo = login_str.split("://")
    loginInfo, hostInfo = dbinfo.split('@')
    userName, passWord = loginInfo.split(':')
    hostInfo, dataBase = hostInfo.split('/')
    host, port = hostInfo.split(':')
    db_infos.setdefault('dbtype', dbtype)
    db_infos.setdefault('username', userName)
    db_infos.setdefault('password', passWord)
    db_infos.setdefault('host', host)
    db_infos.setdefault('port', port)
    db_infos.setdefault('database', dataBase)
    return db_infos


if __name__ == "__main__":
    from setting import hive_db, result_db

    tt = (int(round(time.time() * 1000)))

    dbinfo = getconninfo(result_db)
    sdb = PostgreOpr(dbinfo)
    try:
        # print sdb.initPgSql("aaa",10)
        # sdb.getAllTable()
        #ret = sdb.execsql("INSERT INTO ifeve (taskid, url, result, updatetime) VALUES ('d43325b39c003b4b8949428e65caa52d', 'http://ifeve.com/tag/forkjoin/', 'ifeve.com', 1.541122226356067E9);")
        ret = sdb.execsql("UPDATE ifeve set updatetime = %s where taskid='d43325b39c003b4b8949428e65caa52d'" % (tt))
        print("ret:" + str(ret))
    except Exception as e:
        print(e)
        sdb.rollback()
    else:
        sdb.commit()
    sdb.close()
    print "ok........"
